Imports System.IO
Imports System
Imports System.Collections
Imports System.Runtime.Serialization.Formatters.Soap
Imports System.Runtime.Serialization
Imports System.Runtime.Serialization.Formatters.Binary
Imports System.Data.SqlClient
Imports C3I.CodegenTools
Imports IronPython.Hosting
Imports IronPython.Compiler


Module basSupportCode

    Public Const MaxProperties = 50
    Public Const MAX_PAGES = 4

    Public tgenerateParameters As New GenerateParameters
    Public selectedPage As Integer
    Public fileTypes() As String = {"Template File", "*.tld", "LayoutTemplate", "Database File", "*.dbd", "DatabaseServer", "Generation Properties", "*.gpr", "GenerateParameters", "Database Settings", "*.dbs", "ConfigSettings"}
    Public columnExpressions As New Dictionary(Of String, String)
    Public idDirty(MAX_PAGES) As Boolean, pageObjects(MAX_PAGES) As Object, titleBar(MAX_PAGES) As String
    Public dbServer As DatabaseServer
    Public selectedLevel As Integer
    Public selectedControl As Control, mainForm As Form
    Public listProperties As New frmListProperties
    Dim databaseSchema As DataSchema, findString As String
    Public layoutConfig As New ConfigSettings

    Private engine As PythonEngine, newDatabase As DatabaseStructure

    Public addEntities As Boolean ',processExistingSQLStatements As Boolean
    Public processFieldScripts As ProcessScripts, processTableScripts As ProcessScripts

    Enum ProcessScripts
        NewItemsOnly
        AllItems
        ExistingOnly
        NoItems
    End Enum


    Enum DataEntry
        Template
        Database
        Generation
    End Enum

    ''' <summary>
    ''' Populate database layout treeview control
    ''' </summary>
    ''' <param name="treeView">treeview control to populate</param>
    ''' <param name="server">DatabaseServer object instance to populate view with</param>
    ''' <remarks></remarks>
    Sub CreateDBLayoutTreeView(ByVal treeView As TreeView, ByVal server As DatabaseServer)
        Dim treeroot As TreeNode, treenode As TreeNode, typesnode As TreeNode, nin As Integer
        Dim dbtreeroot As TreeNode

        'clear any old nodes
        treeView.Nodes.Clear()

        treeroot = treeView.Nodes.Add("root", server.Name, "server", "server")

        treeroot.Tag = server

        'loop through each database
        For Each kvpdb As KeyValuePair(Of String, DatabaseStructure) In server.Databases
            Dim database As DatabaseStructure = kvpdb.Value
            dbtreeroot = treeroot.Nodes.Add("db_" & database.Name, database.Name, "app_data", "app_data")
            dbtreeroot.Tag = database

            'add tables  to root
            For Each kpv As KeyValuePair(Of String, TableLayout) In database.TableLayouts
                Dim tableLayout As TableLayout, levelIcon As String
                tableLayout = kpv.Value

                levelIcon = IIf(tableLayout.Properties.ContainsKey("IsView") AndAlso tableLayout.Properties("IsView").Value = "True", "view", "table")

                treenode = dbtreeroot.Nodes.Add("tl_" & tableLayout.Name, tableLayout.Name, levelIcon, levelIcon)
                treenode.Tag = tableLayout

                'add columns
                With tableLayout

                    If Not .Columns Is Nothing Then
                        For Each col As Column In .Columns
                            typesnode = New TreeNode
                            typesnode.Text = col.Name
                            typesnode.Name = "col_" & col.Name
                            typesnode.Tag = col
                            If col.IsKey Then
                                typesnode.ImageKey = "key"
                                typesnode.SelectedImageKey = "key"
                            ElseIf col.Values.ContainsKey("IsAssociation") AndAlso col.Values("IsAssociation").Value = "True" Then

                                If col.Values("AssociationType").Value = "One to Many" Then
                                    typesnode.ImageKey = "1tomany"
                                    typesnode.SelectedImageKey = "1tomany"
                                Else
                                    typesnode.ImageKey = "1toOne"
                                    typesnode.SelectedImageKey = "1toOne"
                                End If
                            Else

                                typesnode.ImageKey = "field"
                                typesnode.SelectedImageKey = "field"
                            End If
                            nin = treenode.Nodes.Add(typesnode)

                        Next
                    End If
                End With
            Next
        Next

        treeView.Select()
        treeView.SelectedNode = treeView.Nodes(0)
    End Sub

    ''' <summary>
    ''' Populate database layout treeview control
    ''' </summary>
    ''' <param name="treeView">treeview control to populate</param>
    ''' <param name="layout">layout object instance to populate view with</param>
    ''' <remarks></remarks>
    Sub CreateTreeView(ByVal treeView As TreeView, ByVal layout As LayoutTemplate)
        Dim nf As Integer, treeroot As TreeNode, nI As Integer, treenode As TreeNode, typesnode As TreeNode, nin As Integer

        treeView.Nodes.Clear()
        treeroot = treeView.Nodes.Add("root", layout.LayoutName, "templatelayout", "templatelayout")
        treeroot.Tag = layout

        'add sections to root
        For nf = 0 To layout.Sections.Count - 1
            treenode = treeroot.Nodes.Add("sec_" & layout.Sections(nf).Description, layout.Sections(nf).Description, "section", "section")
            treenode.Tag = layout.Sections(nf)
            'add sections
            With layout.Sections(nf)

                'add any data types
                If Not .DataTypes Is Nothing Then
                    For nI = 0 To .DataTypes.Count - 1
                        typesnode = New TreeNode
                        typesnode.Text = .DataTypes(nI).Description
                        typesnode.Name = "det_" & .DataTypes(nI).Type
                        typesnode.Tag = .DataTypes(nI)
                        typesnode.ImageKey = "script"
                        typesnode.SelectedImageKey = "script"

                        nin = treenode.Nodes.Add(typesnode)
                    Next

                End If
            End With

        Next

        treeView.Select()
        treeView.SelectedNode = treeView.Nodes(0)

    End Sub

    Sub ShowError(ByVal strMessage As String, ByVal strDetails As String)

        Dim ErrorBox As New frmErrorForm

        ErrorBox.txtMessage.Text = strMessage
        ErrorBox.txtExceptionDetails.Text = strDetails

        ErrorBox.ShowDialog()

    End Sub

    ''' <summary>
    ''' Populate database object 
    ''' </summary>
    ''' <param name="dbServer">object to populate</param>
    ''' <remarks></remarks>
    Sub PopulateServerObjects(ByVal dbServer As DatabaseServer, ByVal databaseList As Generic.List(Of String))
        Dim ProgressBar As New frmProgressBar

        'change cursor 
        Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor

        InitDBSchema(dbServer)

        ProgressBar.Show()
        ProgressBar.ProgressMax = databaseList.Count

        'process each database
        For Each databaseName As String In databaseList

            Try
                ProgressBar.Message = "Processing database " & databaseName
                databaseSchema.DatabaseName = databaseName
                PopulateDatabaseObject(databaseName, dbServer)
                ProgressBar.ProgressBar.PerformStep()
                ProgressBar.ProgressBar.Refresh()

            Catch e As Exception

                ShowError("Error processing database " & databaseName & " Terminating processing ", e.StackTrace)

                Exit For
            End Try


        Next

        ProgressBar.Close()
        Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Arrow

        CreateDBLayoutTreeView(frmMainApp.tvwDatabaseLO, dbServer)
        pageObjects(1) = dbServer

        frmMainApp.tvwDatabaseLO.Select()
        frmMainApp.tvwDatabaseLO.SelectedNode = frmMainApp.tvwDatabaseLO.Nodes(0)

    End Sub

    Function GetDatabaseList(ByVal dbServer As DatabaseServer) As Generic.List(Of String)

        InitDBSchema(dbServer)

        GetDatabaseList = databaseSchema.GetDatabases()

    End Function

    Sub InitDBSchema(ByVal dbServer As DatabaseServer)

        Select Case dbServer.DatabaseType
            Case "MySQL"
                databaseSchema = New MySQLDataSchema()
            Case "SQL Server"
                databaseSchema = New SQLDataSchema()
            Case "PostgreSQL"
                'databaseSchema = New POSTGresSchema()

        End Select

        'set logon credentials
        databaseSchema.UserID = dbServer.UserName
        databaseSchema.Password = dbServer.Password
        databaseSchema.UseAuthentication = dbServer.UseNTAuthentication
        databaseSchema.Server = dbServer.Name
        databaseSchema.Port = dbServer.Port


    End Sub
    ''' <summary>
    ''' BuildError
    ''' </summary>
    ''' Builds an error message based on exception type. 
    ''' <param name="ex"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function BuildError(ByVal ex As Exception) As String
        Dim newObject As Object, typeName As String
        Dim errorMessage As String

        'check if source is IronPython
        If ex.Source = "IronPython" Then

            typeName = ex.GetType().AssemblyQualifiedName

            'determine if certain exceptions, then extract line number
            If typeName.Contains("TabError") Or typeName.Contains("IndentationError") Or typeName.Contains("SyntaxErrorException") Then
                newObject = Convert.ChangeType(ex, Type.GetType(typeName))
                errorMessage = "Error processing Python script on line " & newObject.Line & vbCrLf & ex.Message
            Else
                errorMessage = "Error compiling Python script" & vbCrLf & ex.Message

            End If
        Else
            errorMessage = ex.Message
        End If

        Return errorMessage
    End Function

    ''' <summary>
    ''' Populate individual database object
    ''' </summary>
    ''' <param name="dbName">Name of database</param>
    ''' <param name="dbServer">Parent database server object instance</param>
    ''' <remarks></remarks>
    Sub PopulateDatabaseObject(ByVal dbName As String, ByVal dbServer As DatabaseServer)
        Dim newTableLayout As TableLayout, isDirty As Boolean, evalFunction As Boolean

        'see if database already exists
        If Not dbServer.Databases.ContainsKey(dbName) Then
            newDatabase = New DatabaseStructure
            newDatabase.Name = dbName
            dbServer.Databases.Add(newDatabase.Name, newDatabase)
            newDatabase.ConnectionString = databaseSchema.ConnectionString
        Else
            newDatabase = dbServer.Databases(dbName)
        End If

        If databaseSchema Is Nothing Then
            InitDBSchema(dbServer)
            databaseSchema.DatabaseName = dbName

        End If

        Dim tablesList As Generic.List(Of DBObject) = databaseSchema.GetTables()

        engine = New PythonEngine
        engine.Import("Site")

        Try

            engine.Execute(layoutConfig.PairValues("FunctionCode")(1))
            engine.Globals("BuildAssociations") = addEntities

        Catch ex As Exception

            ShowError(BuildError(ex), ex.StackTrace)

            Exit Sub
        End Try

        'process each database table 
        For Each tableName As DBObject In tablesList
            'see if table already exists
            If Not newDatabase.TableLayouts.ContainsKey(tableName.Name) Then
                newTableLayout = New TableLayout
                newTableLayout.Name = tableName.Name
                newTableLayout.Properties("TableSchema").Value = tableName.Schema
                newDatabase.ConnectionString = databaseSchema.ConnectionString
                newDatabase.Server = databaseSchema.ConnectionString
                newDatabase.TableLayouts.Add(tableName.Name, newTableLayout)

            Else
                newTableLayout = newDatabase.TableLayouts(tableName.Name)
            End If
            newTableLayout = PopulateTable(tableName.Name, tableName.Schema, newTableLayout)
        Next

        Dim copyTables(newDatabase.TableLayouts.Count - 1) As String
        newDatabase.TableLayouts.Keys.CopyTo(copyTables, 0)

        'remove any databases that have been deleted
        For Each findString In copyTables
            Dim findTable As DBObject = tablesList.Find(AddressOf CompString)

            If findTable Is Nothing Or findTable.Name = "" Then
                newDatabase.TableLayouts.Remove(findString)
            End If

        Next
        databaseSchema.BuildEntities = addEntities

        engine.Globals("database") = newDatabase
        engine.Globals("DBProviderName") = databaseSchema.ProviderName


        'build SQL select statement for tables
        For Each table As KeyValuePair(Of String, TableLayout) In newDatabase.TableLayouts
            Dim tableLayout As TableLayout
            tableLayout = table.Value

            engine.Globals("tableLayout") = tableLayout

            'add dirty flag if doesn't exist..
            If Not tableLayout.Properties.ContainsKey("IsDirty") Then
                tableLayout.Properties.Add("IsDirty", New PropertyValue("True", True))
                isDirty = True
            Else
                isDirty = CBool(tableLayout.Properties("IsDirty").Value)
            End If

            For Each pva As PairValue In layoutConfig.FieldFunctions
                Try

                    If pva.Type = "Table" Then
                        evalFunction = True

                        'process only new (non-dirty?) fields
                        If pva.Custom = "New only" And isDirty Then
                            evalFunction = False
                        ElseIf pva.Custom = "Existing" And Not isDirty Then
                            evalFunction = False
                        End If

                        If evalFunction Then
                            'if key doesn't exist in table properties add it
                            If tableLayout.Properties.ContainsKey(pva.PropertyID) Then
                                tableLayout.Properties(pva.PropertyID).Value = engine.Evaluate(pva.Value).ToString
                            Else
                                tableLayout.Properties.Add(pva.PropertyID, New PropertyValue(engine.Evaluate(pva.Value).ToString, False))
                            End If
                        End If

                    End If
                Catch ex As Exception
                    ShowError("Error evaluating function when assigning value to table property :" & pva.PropertyID & vbCrLf & BuildError(ex), ex.ToString)
                End Try

            Next

            tableLayout.Properties("IsDirty").Value = "True"

            If processTableScripts = ProcessScripts.AllItems Then

            End If
        Next

        If processFieldScripts = ProcessScripts.AllItems Or processFieldScripts = ProcessScripts.ExistingOnly Then
            'build SQL select statement for tables
            For Each table As KeyValuePair(Of String, TableLayout) In newDatabase.TableLayouts
                Dim tableLayout As TableLayout
                tableLayout = table.Value

                engine.Globals("tableLayout") = tableLayout


                Dim newcol As Column

                For Each newcol In tableLayout.Columns

                    'add dirty flag if doesn't exist..
                    If Not newcol.Values.ContainsKey("IsDirty") Then
                        newcol.Values.Add("IsDirty", New PropertyValue("True", True))
                        isDirty = True
                    Else
                        isDirty = CBool(newcol.Values("IsDirty").Value)
                    End If


                    For Each kvp As KeyValuePair(Of String, PropertyValue) In newcol.Values
                        engine.Globals(kvp.Key) = GetValue(newcol.Values, kvp.Key)
                    Next

                    engine.Globals("column") = newcol
                    engine.Globals("IsPrimaryKey") = newcol.IsKey
                    engine.Globals("IsForeignKey") = newcol.IsForeignKey
                    engine.Globals("IsVirtualField") = newcol.IsVirtualField
                    engine.Globals("IsAssociation") = ConvBool(newcol.Values("IsAssociation").Value)
                    engine.Globals("IS_NULLABLE") = ConvBool(newcol.Values("IS_NULLABLE").Value)
                    engine.Globals("ISIDENTITY") = ConvBool(newcol.Values("ISIDENTITY").Value)
                    engine.Globals("FieldName") = newcol.Name
                    engine.Globals("BaseDataType") = newcol.BaseDataType
                    engine.Globals("GenericType") = newcol.GenericType
                    engine.Globals("Ignore") = newcol.Ignore
                    engine.Globals("NativeFieldType") = newcol.NativeFieldType

                    For Each pva As PairValue In layoutConfig.FieldFunctions
                        If pva.Type = "Field" Then

                            evalFunction = True
                            'process only new (non-dirty?) fields
                            If pva.Custom = "New only" And isDirty Then
                                evalFunction = False
                            ElseIf pva.Custom = "Existing" And Not isDirty Then
                                evalFunction = False
                            End If
                            If evalFunction Then


                                'if key doesn't exist in column properties add it
                                If newcol.Values.ContainsKey(pva.PropertyID) Then
                                    newcol.Values(pva.PropertyID).Value = engine.Evaluate(pva.Value).ToString
                                Else
                                    newcol.Values.Add(pva.PropertyID, New PropertyValue(engine.Evaluate(pva.Value).ToString, False))
                                End If
                            End If

                        End If
                    Next
                    newcol.Values("IsDirty").Value = "True"
                Next
            Next
        End If

    End Sub

    Function CompString(ByVal tableOb As DBObject) As Boolean
        Return (tableOb.Name = findString)
    End Function
    ''' <summary>
    ''' PopulateTable
    ''' </summary>
    ''' <param name="strTable">Table to populate from</param>
    ''' <param name="tableLayout">Tableobject instance to populate</param>
    ''' <returns>Populate Tablelayout instance</returns>
    ''' <remarks></remarks>
    Function PopulateTable(ByVal strTable As String, ByVal tableSchema As String, ByVal tableLayout As TableLayout) As TableLayout

        Dim strSQLType As String = ""
        Dim strFieldName As String, newcol As Column, colAlreadyExists As Boolean, columnCount As Integer = 0
        Dim columndictionary As Dictionary(Of String, PropertyValue), columnList As New Generic.Dictionary(Of String, String)
        Dim columnsList As New Generic.List(Of Generic.Dictionary(Of String, String)), colIndex As Int16 = 0

        Dim columnTypes As New Dictionary(Of String, String), columnDescriptions As New Dictionary(Of String, String)
        Dim foreignKeys As New Dictionary(Of String, String()), primaryKeys As New Dictionary(Of String, String)
        Dim indexes As New Dictionary(Of String, Index)
        tableLayout.Name = strTable
        databaseSchema.TableName = strTable
        databaseSchema.TableSchema = tableSchema

        databaseSchema.PopulateTableInfo()


        databaseSchema.CheckIfView()
        columnTypes = databaseSchema.ColumnTypes
        tableLayout.Properties("IsView").Value = databaseSchema.IsView.ToString

        tableLayout.ForeignTables.Clear()

        For Each foreignTables() As String In databaseSchema.ForeignTables
            tableLayout.ForeignTables.Add(foreignTables)
        Next

        If Not databaseSchema.IsView Then
            columnDescriptions = databaseSchema.ColumnDescriptions
            primaryKeys = databaseSchema.PrimaryKeys
            foreignKeys = databaseSchema.ForeignKeys
            indexes = databaseSchema.Indexes
        End If

        Try
            columnsList = databaseSchema.ColumnsList

            'loop through each column
            For Each columndictionary2 As Dictionary(Of String, String) In columnsList
                strFieldName = columndictionary2("COLUMN_NAME")

                colAlreadyExists = False
                columnList.Add(strFieldName, strFieldName)

                'check if column already exists in structure, if not add new column
                Dim columnExists As Boolean = False
                colIndex = 0
                For Each col As Column In tableLayout.Columns

                    If col.Name = strFieldName Then
                        columnExists = True
                        Exit For
                    End If
                    colIndex = colIndex + 1
                Next

                If Not columnExists Then
                    newcol = New Column
                    newcol.Ignore = False
                    newcol.Name = strFieldName
                    tableLayout.Columns.Insert(columnCount, newcol)

                    For Each coldictionaryentry As KeyValuePair(Of String, String) In columndictionary2
                        If Not newcol.Values.ContainsKey(coldictionaryentry.Key) Then
                            newcol.Values.Add(coldictionaryentry.Key, New PropertyValue(coldictionaryentry.Value, True))
                        Else
                            newcol.Values(coldictionaryentry.Key).Value = coldictionaryentry.Value
                        End If
                    Next

                    columndictionary = newcol.Values
                    columndictionary("NETDataType").Value = columnTypes(strFieldName)

                Else 'get existing column and update values
                    newcol = tableLayout.Columns(colIndex)

                    If Not colAlreadyExists Then
                        newcol.IsKey = False
                        newcol.IsForeignKey = False
                    End If

                    'merge property values.. this adds new properties and updates existing ones
                    For Each colName As String In columndictionary2.Keys
                        If newcol.Values.ContainsKey(colName) Then
                            newcol.Values(colName) = New PropertyValue(columndictionary2(colName), True) 'columndictionary(colName)
                        Else 'if value didn't exist, it's new and add it 
                            newcol.Values.Add(colName, New PropertyValue(columndictionary2(colName), True))
                        End If

                    Next
                    'update column type
                    newcol.Values("NETDataType").Value = columnTypes(strFieldName)

                End If

                '
                newcol.Values("IS_NULLABLE").Value = ConvBool(newcol.Values("IS_NULLABLE").Value).ToString
                newcol.IsKey = False

                'base data type Date/String/Integer/Double
                newcol.BaseDataType = databaseSchema.GetDataType(newcol.Values("DATA_TYPE").Value)
                'generic data type
                newcol.GenericType = databaseSchema.GetGenericColumnType(newcol.Values("DATA_TYPE").Value)

                'check if primary key
                If primaryKeys.ContainsKey(strFieldName) Then
                    newcol.IsKey = True 'primary key
                End If

                newcol.IsForeignKey = False

                'if column has an index and isn't a key then 
                If indexes.ContainsKey(strFieldName) And Not newcol.IsKey Then
                    newcol.Values("Index").Value = "true"
                Else
                    newcol.Values("Index").Value = "false"
                End If

                'check if foreign key
                If foreignKeys.ContainsKey(strFieldName) Then
                    newcol.IsForeignKey = True
                    newcol.Values("FKTABLE_NAME").Value = foreignKeys(strFieldName)(1)
                    newcol.Values("FTKEY").Value = foreignKeys(strFieldName)(2)
                    newcol.Values("FKConstraintName").Value = foreignKeys(strFieldName)(3)
                    newcol.Values("FKTableSchema").Value = foreignKeys(strFieldName)(0)

                Else
                    'reset foreign key settings, in case it was removed
                End If

                'check if column descriptions
                If columnDescriptions.ContainsKey(strFieldName) Then
                    newcol.Values("Description").Value = columnDescriptions(strFieldName)
                End If

                If Not System.String.IsNullOrEmpty(columndictionary2("CHARACTER_MAXIMUM_LENGTH")) Then
                    newcol.Values("Width").Value = columndictionary2("CHARACTER_MAXIMUM_LENGTH")
                ElseIf Not System.String.IsNullOrEmpty(columndictionary2("NUMERIC_PRECISION")) Then
                    newcol.Values("Width").Value = columndictionary2("NUMERIC_PRECISION")
                Else
                    newcol.Values("Width").Value = "10"
                End If
                columnCount = columnCount + 1
            Next


            Dim columnCopy(tableLayout.Columns.Count - 1) As Column
            'make a copy of columns from list to avoid errors removing items from list
            tableLayout.Columns.CopyTo(columnCopy)

            For Each col As Column In columnCopy
                'remove any non-virtual fields that no longer exist in database structure
                If Not columnList.ContainsKey(col.Name) And Not col.IsVirtualField Then
                    tableLayout.Columns.Remove(col)
                End If
            Next

        Finally
        End Try

        Return tableLayout

    End Function

    Function ConvBool(ByVal value As String) As Boolean

        value = Trim(value.ToUpper)
        If value = "YES" Or value = "TRUE" Or value = "1" Then
            Return True
        Else
            Return False
        End If

    End Function

    Function GetValue(ByVal valueDictionary As Dictionary(Of String, PropertyValue), ByVal valueName As String) As Object

        Dim dictionaryValue As String
        dictionaryValue = valueDictionary(valueName).Value

        If InStr("Index,Width,MultiLine,CharWidth,CharRows,PixelWidth,PixelHeight,MultiLine2,CharWidth2,CharRows2,PixelWidth2,PixelHeight2,MultiLine3,CharWidth3,CharRows3,PixelWidth3,PixelHeight3,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,ORDINAL_POSITION,", valueName & ",", CompareMethod.Text) > 0 Then
            If Not IsNumeric(dictionaryValue) Then
                Return Int(0)
            Else
                Return CInt(dictionaryValue)
            End If

        Else
            Return dictionaryValue
        End If

    End Function


End Module





